import os.path
from tools.mongo_tool import MongoDbLink
import openpyxl
import requests
import pymongo
from bson import ObjectId
from config.configs import TEST_PORT, TEST_DB_HOST, ALPHA_DB_HOST, ALPHA_DB_PORT, DATA_PATH, DESKTOP_PATH
from datetime import datetime
import time
from time import sleep
from tools.funciton import read_xlsx_file, write_xlsx

jw = MongoDbLink(ALPHA_DB_HOST, ALPHA_DB_PORT, db_name='alpha')


def date_timestamp(date):
    date_obj = datetime.strptime(date, "%Y-%m-%d")
    timestamp = time.mktime(date_obj.timetuple())
    return int(str(timestamp * 1000).split('.')[0])


def get_all_spu_sku(code):
    """获取商家所有货号尺码"""
    result = []
    for i in jw.aggregate('stocks', [
        {'$match': {'merchantCode': code, }},
        {'$group': {'_id': {'spu': "$spuId", 'sku': "$skuId"}, 'total': {'$sum': 1, }, }, },
        {'$lookup': {'from': "spus", 'localField': "_id.spu", 'foreignField': "spuId", 'as': "spu", }, },
        {'$lookup': {'from': "skus", 'localField': "_id.sku", 'foreignField': "skuId", 'as': "sku", }, },
        {'$project': {'articlenumber': "$spu.articleNumber", 'size': "$sku.properties", }, },
        {'$unwind': {'path': "$articlenumber"}, },
        {'$unwind': {'path': "$size", }}]):
        result.append([i['articlenumber'], i['size'].split('尺码":"')[1].split('"}')[0]])
    return result


def check_stocks(articleNumber, size, merchantCode, start='2022-01-01', end='2024-12-12', check_num=0):
    spu = [i['spuId'] for i in jw.find('spus', {'articleNumber': articleNumber})][0]
    sku = [i['skuId'] for i in jw.find('skus', {'spuId': spu, "properties": {"$regex": size}})][0]
    startDate = date_timestamp(start)
    endDate = date_timestamp(end)
    speed_in_stock = [i['inStockQty'] for i in jw.find('stocks', {"merchantCode": merchantCode, "skuId": sku,
                                                                  "createdAt": {'$gt': startDate,
                                                                                '$lt': endDate},
                                                                  "warehouseType": "common", "appointType": 1})]
    speed_normal_num = str(sum(speed_in_stock))  # 极速仓正常预约单入库
    speed_fail_in = [i['inStockQty'] for i in jw.find('stocks', {"merchantCode": merchantCode, "skuId": sku,
                                                                 "createdAt": {'$gt': startDate,
                                                                               '$lt': endDate},
                                                                 "warehouseType": "common", "appointType": 3})]
    speed_fail_in_num = str(sum(speed_fail_in))  # 极速仓闪电质检退回入库
    speed_brand_in = [i['inStockQty'] for i in jw.find('stocks', {"merchantCode": merchantCode, "skuId": sku,
                                                                  "createdAt": {'$gt': startDate,
                                                                                '$lt': endDate},
                                                                  "warehouseType": "common",
                                                                  "appointType": {'$in': [8, 9]}})]
    speed_brand_num = str(sum(speed_brand_in))  # 极速仓买家直退入库
    speed_out_back = [i['inStockQty'] for i in jw.find('stocks', {"merchantCode": merchantCode, "skuId": sku,
                                                                  "createdAt": {'$gt': startDate,
                                                                                '$lt': endDate},
                                                                  "warehouseType": "common", "appointType": 2})]
    speed_out_back_num = str(sum(speed_out_back))  # 极速仓调货出库退回入库
    speed_to_flash = [i['qty'] for i in jw.find('stock_out_records',
                                                {"merchantCode": merchantCode, "skuId": sku,
                                                 "createdAt": {'$gt': startDate, '$lt': endDate}, "outStockType": 1})]
    speed_to_flash_num = str(sum(speed_to_flash))  # 极速仓转仓出库
    speed_stock_out = [i['qty'] for i in jw.find('stock_out_records',
                                                 {"merchantCode": merchantCode, "skuId": sku,
                                                  "createdAt": {'$gt': startDate, '$lt': endDate}, "outStockType": 3})]
    speed_stock_out_num = str(sum(speed_stock_out))  # 极速仓调货出库数
    speed_brand_out = [i['qty'] for i in jw.find('stock_out_records',
                                                 {"merchantCode": merchantCode, "skuId": sku,
                                                  "createdAt": {'$gt': startDate, '$lt': endDate}, "outStockType": 8})]
    speed_brand_out_num = str(sum(speed_brand_out))  # 极速仓直退出库数
    flash_normal_in = [i['inStockQty'] for i in jw.find('stocks', {"merchantCode": merchantCode, "skuId": sku,
                                                                   "createdAt": {'$gt': startDate,
                                                                                 '$lt': endDate},
                                                                   "warehouseType": "dewuflash", "appointType": 1})]
    flash_normal_in = str(sum(flash_normal_in))  # 闪电仓正常预约入库
    flash_from_speed = [i['inStockQty'] for i in jw.find('stocks', {"merchantCode": merchantCode, "skuId": sku,
                                                                    "createdAt": {'$gt': startDate,
                                                                                  '$lt': endDate},
                                                                    "warehouseType": "dewuflash", "appointType": 2})]
    flash_from_speed_num = str(sum(flash_from_speed))  # 闪电仓转仓申请入库
    flash_from_refund = [i['inStockQty'] for i in jw.find('stocks', {"merchantCode": merchantCode, "skuId": sku,
                                                                     "createdAt": {'$gt': startDate,
                                                                                   '$lt': endDate},
                                                                     "warehouseType": "dewuflash", "appointType": 3})]
    flash_from_refund_num = str(sum(flash_from_refund))  # 闪电仓客退转入仓入库
    flash_from_brand = [i['inStockQty'] for i in jw.find('stocks', {"merchantCode": merchantCode, "skuId": sku,
                                                                    "createdAt": {'$gt': startDate,
                                                                                  '$lt': endDate},
                                                                    "warehouseType": "dewuflash", "appointType": 4})]
    flash_from_brand_num = str(sum(flash_from_brand))  # 闪电仓直退转闪电入库
    order_no = [i['order_no'] for i in jw.aggregate('dewustatements', [{
        '$match': {"merchantCode": merchantCode, "article_number": articleNumber, 'props': size}
    }, {
        '$lookup': {
            'from': 'orders',
            'localField': 'order_no',
            'foreignField': 'orderNo',
            'as': 'result'}
    }, {
        '$unwind': {'path': "$result"}
    }, {
        '$match': {
            "result.createdAt": {'$gt': startDate, '$lt': endDate}
        }
    }])]
    flash_order_no = str(len(order_no))  # 闪电仓销售数量
    flash_flaw_return = [i['platformUniqueCode'] for i in
                         jw.find('flash_flaw_return', {"merchantCode": merchantCode, "skuId": sku,
                                                       "createdAt": {'$gt': startDate,
                                                                     '$lt': endDate},
                                                       "status": 0})]
    flaw_not_in_num = str(len(flash_flaw_return))  # 闪电质检未退回
    # res = requests.post(url='https://alpha.geehoop.com/api/backend/stock/sku/detail', headers={'Authorization': 'Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjY1MGE0YTg2ODNmOTE1YzM4ZDE4YzY0MCIsImlhdCI6MTcxMjQ2MDc0MiwiZXhwIjoxNzEzMDY1NTQyfQ.f7YDudOwlIpcDHzCpVgNSbuQ0oC7r1qla0DBd_APgBY', 'Content-Type': 'application/json; charset=utf-8'},
    #                     json={"skuId":sku,"warehouseType":"dewuflash","isFail":'false',"filter":{"batchCode":"","uniqueCode":"","merchantCode":merchantCode,"merchantName":"","shelfCode":"","failLevel":"","appointNo":""}})
    # if res.json()['data']['data'] != []:
    #     flash_stock_num = str(res.json()['data']['data'][0]['qtyTotal'])  # 闪电在仓
    # else:
    #     flash_stock_num = '0'
    # result = requests.post(url='https://alpha.geehoop.com/api/backend/stock/sku/detail', headers={
    #     'Authorization': 'Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjY1MGE0YTg2ODNmOTE1YzM4ZDE4YzY0MCIsImlhdCI6MTcxMjQ2MDc0MiwiZXhwIjoxNzEzMDY1NTQyfQ.f7YDudOwlIpcDHzCpVgNSbuQ0oC7r1qla0DBd_APgBY',
    #     'Content-Type': 'application/json; charset=utf-8'},
    #                     json={"skuId": sku, "warehouseType": "common", "isFail": '',
    #                           "filter": {"batchCode": "", "uniqueCode": "", "merchantCode": merchantCode,
    #                                      "merchantName": "", "shelfCode": "", "failLevel": "", "appointNo": ""}})
    # if result.json()['data']['data'] != []:
    #     speed_stock_num = str(res.json()['data']['data'][0]['qtyTotal'])  # 极速仓在仓
    # else:
    #     speed_stock_num = '0'
    speed_num = str(
        int(speed_normal_num) + int(speed_fail_in_num) + int(speed_brand_num) + int(speed_out_back_num) - int(
            speed_to_flash_num) - int(speed_stock_out_num) - int(speed_brand_out_num))
    flash_num = str(
        int(flash_normal_in) + int(flash_from_speed_num) + int(flash_from_refund_num) + int(flash_from_brand_num) - int(
            flash_order_no) - check_num)
    return [merchantCode, articleNumber, size, start, end, speed_normal_num, speed_fail_in_num, speed_brand_num,
            speed_out_back_num, speed_to_flash_num, speed_stock_out_num, speed_brand_out_num, flash_normal_in,
            flash_from_speed_num, flash_from_refund_num, flash_from_brand_num, flash_order_no, str(check_num),
            flaw_not_in_num, speed_num, flash_num]
    # write_xlsx(['商家名称','货号','尺码','开始日期','结束日期','极速仓正常预约入库','质检退回入库','直退入库','调货出库退回','转仓出库','调货出库','直退出库','闪电仓预约入库','闪电仓转仓入库','客退转入仓','直退转闪电','闪电仓销售','货损/取回/超卖','质检未退回','闪电在仓','极速在仓','极速仓理论剩余库存','闪电仓理论剩余库存'],
    #            [[merchantCode,articleNumber,size,start,end,speed_normal_num,speed_fail_in_num,speed_brand_num,speed_out_back_num,speed_to_flash_num,speed_stock_out_num,speed_brand_out_num,flash_normal_in,flash_from_speed_num,flash_from_refund_num,flash_from_brand_num,flash_order_no,str(check_num),flaw_not_in_num,flash_stock_num,speed_stock_num,speed_num,flash_num]], f'{merchantCode}_{articleNumber}_{size}.xlsx')


def check_into_file(code, start='2022-01-01', end='2024-04-01', check_num=0):
    data = []
    for res in get_all_spu_sku(code):
        data.append(check_stocks(res[0], res[1], code, start, end, check_num))
    # print(data)
    write_xlsx(['商家编号', '货号', '尺码', '开始日期', '结束日期', '极速仓正常预约入库', '质检退回入库', '直退入库',
                '调货出库退回', '转仓出库', '调货出库', '直退出库', '闪电仓预约入库', '闪电仓转仓入库', '客退转入仓',
                '直退转闪电', '闪电仓销售', '货损/取回/超卖', '质检未退回', '极速仓理论剩余库存', '闪电仓理论剩余库存'],
               data,
               f'{code}库存核对.xlsx')

# check_into_file('1036')
